import sqlite3 as db
import os
from pymysql import Error
from flask import Flask, request, jsonify, Response
from flask_cors import CORS

app = Flask(__name__)
CORS(app, resources={
  r"/*": {
    "origins": "http://localhost:5173",
    "methods": ["GET", "POST", "PUT", "DELETE"],
    "allowed_headers": ["Content-Type", "Authorization"],
    "supports_credentials": True
  }
})

eventor = {
    'status': False,
    'content': ''
}


######funcion#########

def data_base_init():
    if os.path.exists('./webnotes.db'):
        return
    else:
        print("DataBase init……")
        connection = db.connect('./webnotes.db')
        cursor = connection.cursor()
        sql = '''
                CREATE TABLE message (
                id INTEGER PRIMARY KEY AUTOINCREMENT,  
                info TEXT,                             
                status INTEGER DEFAULT 1 CHECK(status IN (0, 1))
            );
            '''
        cursor.execute(sql)
        connection.commit()

def sql(type, value):
    connection = db.connect('./webnotes.db')
    cursor = connection.cursor()
    if type == 'insert':
        sql = "insert into message(info) values(?)"
        cursor.execute(sql, (value,))  
        connection.commit()
        connection.close()
    elif type == 'get':
        sql = "select id, info from message where status = '1' order by id  desc limit ?, 20;"
        val = value * 20
        cursor.execute(sql, (val,))
        res = cursor.fetchall()
        connection.close()
        return res
    elif type == 'count':
        sql = "select count(*) from message where status = '1';"
        cursor.execute(sql)
        res = cursor.fetchone()
        connection.close()
        return int(res[0])
    elif type == 'delete':
        sql = "update message set status = 0 where id = ?;"
        cursor.execute(sql, (value, ))
        connection.commit()
        connection.close()
        return

    

####router#####
@app.route('/api/events')
def stream_events():
    def generate():
        while True:
            if eventor['status']:
                yield f"data: {eventor['content']}\n\n"
                eventor['status'] = False
                eventor['content'] = True
    return Response(generate(), mimetype='text/event-stream')


@app.route('/api/upload', methods=['POST'])
def upload():
    data = request.json
    sql('insert', data['msg'])
    eventor['content'] = 'freshInfo'
    eventor['status'] = True
    return jsonify({
        'status': 1,
        'msg': ''
    })

@app.route('/api/getdata', methods=['POST'])
def getdata():
    '''
    page: number
    '''
    data = request.json
    response = sql('get', data['page'])
    totalCount = sql('count', 0)
    return {
        'status': 1,
        'total': totalCount,
        'msg': response
    }

@app.route('/api/deleteOne', methods=['POST'])
def deleteOne():
    '''
    id: number
    '''
    data = request.json
    sql('delete', data['id'])
    eventor['content'] = 'freshInfo'
    eventor['status'] = True
    return {
        "status": 1,
        "msg": ''
    }



if __name__ == '__main__':
    data_base_init()
    app.run(debug=True, port=5000)